﻿using System;
using System.Collections.Generic;
using BAC.Excel.Engine;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Metadata;
using System.Data;
using Microsoft.Xrm.Sdk.Metadata.Query;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Messages;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
namespace BAC.Operations.Engine
{
    public class CrmOperationsHelper
    {
        public static EntityMetadataCollection GetAllValidEntityCollection(IOrganizationService _serviceProxy)
        {
            String[] defaultLookupEntities = new String[] {"account"	
		,"activitypointer"	
		,"appointment"	
		,"businessunit"	
		,"campaign"	
		,"campaignactivity"	
		,"campaignresponse"	
		,"competitor"	
		,"constraintbasedgroup"	
		,"contact"	
		,"contract"	
		,"contractdetail"	
		,"contracttemplate"	
		,"customeraddress"	
		,"discounttype"	
		,"email"	
		,"emailserverprofile"	
		,"equipment"	
		,"fax"	
		,"goal"	
		,"goalrollupquery"	
		,"importmap"	
		,"incident"	
		,"invoice"	
		,"kbarticle"	
		,"kbarticletemplate"	
		,"lead"	
		,"letter"	
		,"list"	
		,"mailbox"	
		,"mailmergetemplate"	
		,"mbs_pluginprofile"	
		,"metric"	
		,"msdyn_postalbum"	
		,"msdyn_postconfig"	
		,"msdyn_postruleconfig"	
		,"msdyn_wallsavedquery"	
		,"msdyn_wallsavedqueryusersettings"	
		,"opportunity"	
		,"partnerapplication"	
		,"phonecall"	
		,"pricelevel"	
		,"product"	
		,"queue"	
		,"quote"	
		,"recurringappointmentmaster"	
		,"relationshiprole"	
		,"resourcespec"	
		,"role"	
		,"salesliterature"	
		,"salesorder"	
		,"service"	
		,"serviceappointment"	
		,"sharepointdocumentlocation"	
		,"sharepointsite"	
		,"site"	
		,"subject"	
		,"systemuser"	
		,"task"	
		,"team"	
		,"territory"	
		,"transactioncurrency"	
		,"uom"	
		,"uomschedule"	
		,"workflow"	};
            // specifying EntityFilter to only include entity having SchemaName as contact
            MetadataFilterExpression EntityFilter = new MetadataFilterExpression(LogicalOperator.Or);
            EntityFilter.Conditions.Add((new MetadataConditionExpression("LogicalName", MetadataConditionOperator.In, defaultLookupEntities)));
            EntityFilter.Conditions.Add((new MetadataConditionExpression("IsCustomEntity", MetadataConditionOperator.Equals, true)));
            EntityFilter.Conditions.Add((new MetadataConditionExpression("CanCreateAttributes", MetadataConditionOperator.Equals, true)));

            // specfying EntityProperties to include the properties to be retrieved
            MetadataPropertiesExpression EntityProperties = new
            MetadataPropertiesExpression() { AllProperties = false };
            EntityProperties.PropertyNames.Add("DisplayName");
            EntityProperties.PropertyNames.Add("IsValidForAdvancedFind");
            EntityProperties.PropertyNames.Add("IsCustomEntity");
            EntityProperties.PropertyNames.Add("CanCreateAttributes");
            EntityProperties.PropertyNames.Add("ManyToOneRelationships");
            EntityProperties.PropertyNames.Add("OneToManyRelationships");

            //An entity query expression to combine the filter expressions and property expressions for the query.
            EntityQueryExpression entityQueryExpression = new
            EntityQueryExpression()
            {
                Criteria = EntityFilter,
                Properties = EntityProperties
            };

            RetrieveMetadataChangesRequest retrieveMetadataChangesRequest = new
            RetrieveMetadataChangesRequest()
            {
                Query = entityQueryExpression
            };
            RetrieveMetadataChangesResponse retrieveMetadataChangesResponse = (RetrieveMetadataChangesResponse)_serviceProxy.Execute(retrieveMetadataChangesRequest);
            EntityMetadataCollection entityMetadataCollection = retrieveMetadataChangesResponse.EntityMetadata;
            return entityMetadataCollection;

        }
        public static List<string> OperationErrorList = new List<string>();
        public static EntityCollection GetPublishersList(IOrganizationService OrgService)
        {
            ConditionExpression condExpress1 = new ConditionExpression("isreadonly", ConditionOperator.Equal, false);
            ConditionExpression condExpress2 = new ConditionExpression("customizationprefix", ConditionOperator.NotEqual, "none");
            FilterExpression filterExpression1 = new FilterExpression(LogicalOperator.And);
            filterExpression1.AddCondition(condExpress1);
            filterExpression1.AddCondition(condExpress2);
            QueryExpression query = new QueryExpression()
            {
                EntityName = "publisher",
                ColumnSet = new ColumnSet(true),
                Criteria = filterExpression1

            };

            EntityCollection result = OrgService.RetrieveMultiple(query);

            return result;
        }
        static Dictionary<string, string> lookupEntitySchemaNameResolver = new Dictionary<string, string>();
        static List<Exception> innerExceptions = new List<Exception>();
        #region Attribute Building Methods

        public static void ProcessAttributes(DataTable gridDataTable, IOrganizationService service, string entityName, Dictionary<string, string> lookupEntityNameResolver, out List<string> ErrorList)
        {
            List<Exception> innerExceptions = new List<Exception>();
            OperationErrorList = new List<string>();
            lookupEntitySchemaNameResolver = lookupEntityNameResolver;
            if (service != null && !string.IsNullOrEmpty(entityName))
            {
                List<DataRow> rowList = new List<DataRow>();
                foreach (DataRow dataRow in gridDataTable.Rows)
                {
                    rowList.Add(dataRow);
                }
                ParallelOptions opt = new ParallelOptions();
                opt.MaxDegreeOfParallelism = 10;
                Task createAttributeTask = Task.Factory.StartNew(() =>
                {

                    try
                    {
                        Parallel.ForEach(rowList, opt, row =>
                        {
                            int rowNumber = rowList.IndexOf(row) + 1;
                            try
                            {
                                AttributeMetadata attribute = BuildAttributeMetadata(row, service, entityName);
                                if (attribute != null)
                                {
                                    lock (service)
                                    {
                                        CreateAttribute(attribute, entityName, service);
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                innerExceptions.Add(new Exception("[OPERATION FAILED: ROW{" + rowNumber + "}; ERROR{" + ex.Message + "}]"));
                            }
                        });
                        if (innerExceptions.Count > 0)
                        {
                            throw new AggregateException(innerExceptions);

                        }
                    }
                    catch (AggregateException ae)
                    {
                        foreach (var ex in ae.InnerExceptions)
                        {
                            OperationErrorList.Add(ex.Message);
                        }

                    }

                });
                createAttributeTask.Wait();
            }
            ErrorList = OperationErrorList;
        }

        private static AttributeMetadata BuildAttributeMetadata(DataRow row, IOrganizationService service, string entityName)
        {
            AttributeMetadata attribute = null;
            string attributeType = row[ExcelConstants.AttributeTypeHeader].ToString();
            switch (attributeType)
            {

                case "Single Line of Text":
                    attribute = BuildSingleLineOfTextAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Option Set":
                    CreatePickListAttribute(row, service, entityName);
                    //if (attribute != null)
                    //    return attribute;
                    break;
                case "Two Options":
                    attribute = BuildBooleanAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Whole Number":
                    attribute = BuildIntegerAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Floating Point Number":
                    attribute = BuildDoubleAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Decimal Number":
                    attribute = BuildDecimalAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Currency":
                    attribute = BuildMoneyAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Multiple Lines of Text":
                    attribute = BuildMemoAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Date and Time":
                    attribute = BuildDateTimeAttribute(row);
                    if (attribute != null)
                        return attribute;
                    break;
                case "Lookup":
                    CreateLookupAttribute(row, service, entityName);
                    break;
                default:
                    throw new Exception("Invalid Attribute Type");

            };
            return null;
        }

        private static void CreateAttribute(AttributeMetadata attribute, string entityName, IOrganizationService service)
        {
            if (attribute != null && !string.IsNullOrEmpty(entityName) && service != null)
            {
                string attributeName = attribute.SchemaName;
                CreateAttributeRequest createAttributeRequest = new CreateAttributeRequest
                {
                    EntityName = entityName,
                    Attribute = attribute
                };

                try
                {
                    // Execute the request.
                    service.Execute(createAttributeRequest);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        private static BooleanAttributeMetadata BuildBooleanAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strOptions = row[ExcelConstants.OptionsHeader].ToString();
            if (!string.IsNullOrEmpty(strOptions))
            {
                string[] arrayOptions = strOptions.Split(new Char[] { ';' });
                string[] option1 = arrayOptions[0].Split(new Char[] { ',' });
                string[] option2 = arrayOptions[1].Split(new Char[] { ',' });
                string option1Name = option1[0];
                string option2Name = option2[0];
                int option1Value = -1;
                int option2Value = -1;
                if (Int32.TryParse(option1[1], out option1Value) && Int32.TryParse(option1[1], out option2Value) && !string.IsNullOrEmpty(option1Name) && !string.IsNullOrEmpty(option2Name))
                {
                    BooleanAttributeMetadata booleanAttribute = new BooleanAttributeMetadata
                    {
                        // Set base properties
                        SchemaName = strName,
                        DisplayName = new Label(strDisplayName, 1033),
                        RequiredLevel = requiredLevel,
                        Description = new Label(strDescription, 1033),
                        IsAuditEnabled = auditValue,
                        IsSecured = securityValue,
                        OptionSet = new BooleanOptionSetMetadata(
                                    new OptionMetadata(new Label(option1Name, 1033), option1Value),
                                    new OptionMetadata(new Label(option2Name, 1033), option2Value)
                                    )


                    };
                    return booleanAttribute;
                }
            }
            else
            {
                throw new Exception("No option value entered");
            }
            return null;
        }

        private static DateTimeAttributeMetadata BuildDateTimeAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strFormat = row[ExcelConstants.FormatHeader].ToString();
            DateTimeFormat formatValue = GetDateTimeFormatFromCellValue(strFormat);
            string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);
            DateTimeAttributeMetadata dtAttribute = new DateTimeAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label(strDisplayName, 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                Format = formatValue,
                ImeMode = ImeModeValue
            };
            return dtAttribute;
        }

        private static DecimalAttributeMetadata BuildDecimalAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strMaxValue = row[ExcelConstants.MaximumValueHeader].ToString();
            string strMinValue = row[ExcelConstants.MinimumValueHeader].ToString();
            string strPrecision = row[ExcelConstants.PrecisionHeader].ToString();
            int precision = GetIntPrecisonFromCellValue(strPrecision);
            string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);
            DecimalAttributeMetadata decimalAttribute = new DecimalAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label(strDisplayName, 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                MaxValue = Convert.ToInt32(strMaxValue),
                MinValue = Convert.ToInt32(strMinValue),
                Precision = precision,
                ImeMode = ImeModeValue

            };
            return decimalAttribute;
        }

        private static DoubleAttributeMetadata BuildDoubleAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strMaxValue = row[ExcelConstants.MaximumValueHeader].ToString();
            string strMinValue = row[ExcelConstants.MinimumValueHeader].ToString();
            string strPrecision = row[ExcelConstants.PrecisionHeader].ToString();
            int precision = GetIntPrecisonFromCellValue(strPrecision);
            string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);

            DoubleAttributeMetadata doubleAttribute = new DoubleAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label(strDisplayName, 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                MaxValue = Convert.ToInt32(strMaxValue),
                MinValue = Convert.ToInt32(strMinValue),
                Precision = precision,
                ImeMode = ImeModeValue,

            };
            return doubleAttribute;
        }

        private static IntegerAttributeMetadata BuildIntegerAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strMaxValue = row[ExcelConstants.MaximumValueHeader].ToString();
            string strMinValue = row[ExcelConstants.MinimumValueHeader].ToString();
            string strPrecision = row[ExcelConstants.PrecisionHeader].ToString();
            int precision = GetIntPrecisonFromCellValue(strPrecision);
            //string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            //ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);
            int int32MaxValue = Int32.MaxValue;
            int int32MinValue = Int32.MinValue;
            if (!Int32.TryParse(strMaxValue, out int32MaxValue))
            {
                int32MaxValue = Int32.MaxValue;
            }
            else
            {

            }
            if (!Int32.TryParse(strMinValue, out int32MinValue))
            {
                int32MinValue = Int32.MinValue;
            }
            IntegerAttributeMetadata integerAttribute = new IntegerAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label(strDisplayName, 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                MaxValue = int32MaxValue,
                MinValue = int32MinValue

            };
            return integerAttribute;
        }

        private static void CreateLookupAttribute(DataRow row, IOrganizationService service, string referencingEntity)
        {
            try
            {
                lock (service)
                {

                    string strName = row[ExcelConstants.NameHeader].ToString();
                    string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
                    string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
                    AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
                    string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
                    string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
                    string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
                    string strRefrencedEntity = row[ExcelConstants.LookupTypeHeader].ToString();
                    string strRelationshipSchemaName = row[ExcelConstants.RelationshipNameHeader].ToString();
                    BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
                    bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
                    string referencedEntity = string.Empty;
                    string error = string.Empty;
                    if (!string.IsNullOrEmpty(strRefrencedEntity) && lookupEntitySchemaNameResolver.ContainsKey(strRefrencedEntity))
                    {
                        referencedEntity = lookupEntitySchemaNameResolver[strRefrencedEntity].ToString();
                    }
                    bool eligibleCreateOneToManyRelationship = EligibleCreateOneToManyRelationship(service, referencedEntity, referencingEntity);

                    //AddErrorToErrorList(error);
                    if (eligibleCreateOneToManyRelationship)
                    {
                        CreateOneToManyRequest createOneToManyRelationshipRequest =
                            new CreateOneToManyRequest
                            {
                                OneToManyRelationship =
                                new OneToManyRelationshipMetadata
                                {
                                    ReferencedEntity = referencedEntity,
                                    ReferencingEntity = referencingEntity,
                                    SchemaName = strRelationshipSchemaName,
                                    AssociatedMenuConfiguration = new AssociatedMenuConfiguration
                                    {
                                        Behavior = AssociatedMenuBehavior.UseCollectionName,
                                        Group = AssociatedMenuGroup.Details,
                                        Label = new Label(referencedEntity, 1033),
                                        Order = 10000

                                    },
                                    CascadeConfiguration = new CascadeConfiguration
                                    {
                                        Assign = CascadeType.NoCascade,
                                        Delete = CascadeType.RemoveLink,
                                        Merge = CascadeType.Cascade,
                                        Reparent = CascadeType.NoCascade,
                                        Share = CascadeType.NoCascade,
                                        Unshare = CascadeType.NoCascade

                                    }
                                },
                                Lookup = new LookupAttributeMetadata
                                {
                                    // Set base properties
                                    SchemaName = strName,
                                    DisplayName = new Label(strDisplayName, 1033),
                                    RequiredLevel = requiredLevel,
                                    Description = new Label(strDescription, 1033),
                                    IsAuditEnabled = auditValue,
                                    IsSecured = securityValue
                                }
                            };


                        CreateOneToManyResponse createOneToManyRelationshipResponse =
                            (CreateOneToManyResponse)service.Execute(
                            createOneToManyRelationshipRequest);
                        //return lookupAttribute;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private static MemoAttributeMetadata BuildMemoAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strMaxLength = row[ExcelConstants.MaximumLengthHeader].ToString();
            string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);

            MemoAttributeMetadata memoAttribute = new MemoAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label("Sample String", 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                MaxLength = Convert.ToInt32(strMaxLength),
                ImeMode = ImeModeValue,
            };
            return memoAttribute;
        }

        private static MoneyAttributeMetadata BuildMoneyAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strMaxValue = row[ExcelConstants.MaximumValueHeader].ToString();
            string strMinValue = row[ExcelConstants.MinimumValueHeader].ToString();
            string strPrecision = row[ExcelConstants.PrecisionHeader].ToString();
            int precisionSource = GetPrecisionSourceFromCellValue(strPrecision);
            int precision = GetIntPrecisonFromCellValue(strPrecision);
            string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);
            MoneyAttributeMetadata moneyAttribute = new MoneyAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label(strDisplayName, 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                MaxValue = Convert.ToInt32(strMaxValue),
                MinValue = Convert.ToInt32(strMinValue),
                //TODO: Need to set the proper default precision for currency type 
                Precision = precision,
                ImeMode = ImeModeValue,
            };
            return moneyAttribute;
        }

        private static void CreatePickListAttribute(DataRow row, IOrganizationService service, string referencingEntity)
        {
            try
            {
                lock (service)
                {
                    string strName = row[ExcelConstants.NameHeader].ToString();
                    string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
                    string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
                    AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
                    string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
                    string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
                    string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
                    BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
                    bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
                    string strOptions = row["Options"].ToString();
                    string[] arrayOptions = strOptions.Split(new Char[] { ';' });
                    //OPTION Format: Option1Label,Option1Value;Option2Label,Option2Value
                    OptionMetadataCollection objOptionMetadataCollection = new OptionMetadataCollection();
                    foreach (string Option in arrayOptions)
                    {
                        string[] arrayOptionProperties = Option.Split(new Char[] { ',' });
                        string optionLabel = arrayOptionProperties[0];
                        int optionValue = Int32.MinValue;
                        Regex regex = new Regex(@"^\d+$");
                        bool isNumber = regex.IsMatch(arrayOptionProperties[1]);
                        Int32.TryParse(arrayOptionProperties[1], out optionValue);
                        if (isNumber && optionValue >= 0 && !string.IsNullOrEmpty(optionLabel))
                        {
                            OptionMetadata objOptionMetadata = new OptionMetadata(new Label(optionLabel, 1033), optionValue);
                            objOptionMetadataCollection.Add(objOptionMetadata);
                        }
                        else
                        {
                            throw new Exception("Invalid Option:'" + Option + "'");
                        }
                    }

                    if (objOptionMetadataCollection.Count > 0)
                    {
                        PicklistAttributeMetadata pickListAttribute =
                            new PicklistAttributeMetadata
                            {
                                // Set base properties
                                SchemaName = strName,
                                DisplayName = new Label(strDisplayName, 1033),
                                RequiredLevel = requiredLevel,
                                Description = new Label(strDescription, 1033),
                                IsAuditEnabled = auditValue,
                                IsSecured = securityValue,
                                OptionSet = new OptionSetMetadata
                                {
                                    IsGlobal = false,
                                    OptionSetType = OptionSetType.Picklist,
                                    Options = 
                                {
                                    objOptionMetadataCollection[0]
                                }
                                }
                            };
                        //Create the Picklist attribute with only one option
                        CreateAttribute(pickListAttribute, referencingEntity, service);
                        int counter = 0;
                        foreach (OptionMetadata option in objOptionMetadataCollection)
                        {

                            if (counter > 0)// To skip insertion of first option
                            {
                                InsertOptionValueRequest insertOptionValueRequest =
                                new InsertOptionValueRequest
                                {
                                    AttributeLogicalName = strName,
                                    EntityLogicalName = referencingEntity,
                                    Label = option.Label,
                                    Value = option.Value
                                };

                                // Execute the request.
                                service.Execute(insertOptionValueRequest);
                            }
                            counter++;
                        }
                    }
                    else
                    {
                        throw new Exception("No or invalid Option");
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        private static StringAttributeMetadata BuildSingleLineOfTextAttribute(DataRow row)
        {
            string strName = row[ExcelConstants.NameHeader].ToString();
            string strDisplayName = row[ExcelConstants.DisplayNameHeader].ToString();
            string strRequiredLevel = row[ExcelConstants.RequiredLevelHeader].ToString();
            AttributeRequiredLevelManagedProperty requiredLevel = GetRequiredLevelFromCellValue(strRequiredLevel);
            string strDescription = row[ExcelConstants.DescriptionHeader].ToString();
            string strAuditing = row[ExcelConstants.AuditingHeader].ToString();
            string strFieldSecurity = row[ExcelConstants.FieldSecurityHeader].ToString();
            BooleanManagedProperty auditValue = GetAuditValueFromCellValue(strAuditing);
            bool securityValue = GetBooleanFromCellValue(strFieldSecurity, "security");
            string strMaxLength = row[ExcelConstants.MaximumLengthHeader].ToString();
            string strFormat = row[ExcelConstants.FormatHeader].ToString();
            StringFormat format = GetStringFormatFromCellValue(strFormat);
            string strIMEMode = row[ExcelConstants.IMEModeHeader].ToString();
            ImeMode ImeModeValue = GetImeModeFromCellValue(strIMEMode);

            StringAttributeMetadata stringAttribute = new StringAttributeMetadata
            {
                // Set base properties
                SchemaName = strName,
                DisplayName = new Label(strDisplayName, 1033),
                RequiredLevel = requiredLevel,
                Description = new Label(strDescription, 1033),
                IsAuditEnabled = auditValue,
                IsSecured = securityValue,
                // Set extended properties
                MaxLength = Convert.ToInt32(strMaxLength),
                Format = format,
                ImeMode = ImeModeValue
            };
            return stringAttribute;
        }

        private static bool EligibleCreateOneToManyRelationship(IOrganizationService service, string referencedEntity, string referencingEntity)
        {
            //Checks whether the specified entity can be the primary entity in one-to-many
            //relationship.
            if (string.IsNullOrEmpty(referencedEntity))
            {
                throw new Exception("Referenced Entity value not selected");
            }
            else if (string.IsNullOrEmpty(referencingEntity))
            {
                throw new Exception("Invalid Service Proxy,Referencing Entity values not selected");
            }
            else if (service != null && !string.IsNullOrEmpty(referencedEntity) && !string.IsNullOrEmpty(referencingEntity))
            {
                CanBeReferencedRequest canBeReferencedRequest = new CanBeReferencedRequest
                {
                    EntityName = referencedEntity
                };

                CanBeReferencedResponse canBeReferencedResponse =
                    (CanBeReferencedResponse)service.Execute(canBeReferencedRequest);

                if (!canBeReferencedResponse.CanBeReferenced)
                {
                    throw new Exception(string.Format("Entity {0} can't be the primary entity in this one-to-many relationship", referencedEntity));
                }

                //Checks whether the specified entity can be the referencing entity in one-to-many
                //relationship.
                CanBeReferencingRequest canBereferencingRequest = new CanBeReferencingRequest
                {
                    EntityName = referencingEntity
                };

                CanBeReferencingResponse canBeReferencingResponse =
                    (CanBeReferencingResponse)service.Execute(canBereferencingRequest);

                if (!canBeReferencingResponse.CanBeReferencing)
                {
                    throw new Exception(string.Format("Entity {0} can't be the referencing entity in this one-to-many relationship", referencingEntity));
                }


                if (canBeReferencedResponse.CanBeReferenced == true
                    && canBeReferencingResponse.CanBeReferencing == true)
                {

                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }

        }

        #endregion


        #region Attribute Build Helper Methods

        private static ImeMode GetImeModeFromCellValue(string strImeMode)
        {

            switch (strImeMode)
            {

                case "auto":
                    return ImeMode.Auto;
                case "inactive":
                    return ImeMode.Inactive;
                case "active":
                    return ImeMode.Active;
                case "disabled":
                    return ImeMode.Disabled;
                default:
                    return ImeMode.Auto;
            };

        }

        private static bool GetBooleanFromCellValue(string strValue, string property)
        {
            if (strValue == "Enable")
            {
                return true;
            }
            return false;
        }

        private static BooleanManagedProperty GetAuditValueFromCellValue(string strValue)
        {
            if (strValue == "Disable")
            {
                return new BooleanManagedProperty(false);
            }
            return new BooleanManagedProperty(true);
        }

        private static DateTimeFormat GetDateTimeFormatFromCellValue(string strValue)
        {
            if (strValue == "Date Only")
            {
                return DateTimeFormat.DateOnly;
            }
            return DateTimeFormat.DateAndTime;
        }

        private static StringFormat GetStringFormatFromCellValue(string strValue)
        {
            if (strValue == "Text Area")
            {
                return StringFormat.TextArea;
            }
            if (strValue == "URL")
            {
                return StringFormat.Url;
            }
            if (strValue == "Ticker Symbol")
            {
                return StringFormat.TickerSymbol;
            }
            if (strValue == "Phone")
            {
                return StringFormat.Phone;
            }
            return StringFormat.Text;

        }

        private static AttributeRequiredLevelManagedProperty GetRequiredLevelFromCellValue(string strValue)
        {
            if (strValue == "Business Recommended")
            {
                return new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.Recommended);
            }
            if (strValue == "Business Required")
            {
                return new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.ApplicationRequired);
            }
            return new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None);
        }

        private static int GetIntPrecisonFromCellValue(string strValue)
        {
            int precision;
            bool isNumeric = int.TryParse(strValue, out precision);
            if (isNumeric)
            {
                return precision;
            }
            return 2;
        }

        private static int GetPrecisionSourceFromCellValue(string strValue)
        {
            int precision;
            bool isNumeric = int.TryParse(strValue, out precision);
            if (!isNumeric)
            {
                if (strValue == "Pricing Decimal Precision")
                {
                    precision = 1;
                    return precision;
                }
                else if (strValue == "Currency Precision")
                {
                    precision = 2;
                    return precision;
                }

            }
            return 0;
        }

        #endregion

        public static void AddErrorToErrorList(string error)
        {
            if (!string.IsNullOrEmpty(error))
            {
                OperationErrorList.Add(error);
                error = string.Empty;
            }
        }
    }

}
